﻿Imports System.Data.SqlClient
Imports CrystalDecisions.CrystalReports.Engine

Public Class frmProductionStatementSalesmen

    Sub FillStaffList()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim ProducersList As ArrayList = GetProducersList()

            Me.CombSalesman.Items.Clear()

            For Each Producer As String In ProducersList
                Me.CombSalesman.Items.Add(Producer)
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub frmTargetStaff_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized

        FillStaffList()
    End Sub

    Private Sub btnGenerate_Click(sender As System.Object, e As System.EventArgs) Handles btnGenerate.Click
        Me.ErrProv.Clear()

        If Me.CombSalesman.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.CombSalesman, "Please select a valid salesman from the list")

        Else
            If Me.RStatement.Checked = True Then
                ShowStatement()

            ElseIf Me.RSummary.Checked = True Then
                ShowSummary()
            End If
        End If
    End Sub

    Sub ShowStatement()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim dap As New SqlDataAdapter("Select * From Policies Where " & _
                                          "    IssueDate>=N'" & Me.DTPPeriodFrom.Value.ToShortDateString & " 00:00:00' " & _
                                          "and IssueDate<=N'" & Me.DTPPeriodTo.Value.ToShortDateString & " 23:59:59' and DoneFin=1" & _
                                          "and SNo In (Select PolNo From PoliciesProducers Where Producer=@Producer)", cnn)

            Dim dap1 As New SqlDataAdapter("Select * From PoliciesProducers Where Producer=@Producer", cnn)

            Dim das As New DsPolicies

            dap.SelectCommand.Parameters.AddWithValue("@Producer", Me.CombSalesman.Text)
            dap1.SelectCommand.Parameters.AddWithValue("@Producer", Me.CombSalesman.Text)

            dap.Fill(das, "Policies")
            dap1.Fill(das, "PoliciesProducers")

            Dim rpt As New ProductionStatementSalesmen
            rpt.SetDataSource(das)

            'Create Parameters
            Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
            Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            Dim crParameterFieldLocation As ParameterFieldDefinition
            Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
            crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields

            'Salesman PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("Salesman")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.CombSalesman.Text
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            'MinDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MinDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodFrom.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)


            'MaxDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MaxDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodTo.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            Me.CrystalReportViewer1.ReportSource = rpt

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub ShowSummary()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand
            Dim Reader As SqlDataReader
            Dim das As New DsStaffTarget

            cmd.Connection = cnn

            cnn.Open()
            For i As Integer = 1 To 12
                cmd.CommandText = "Select IsNull(dbo.GetPremTargetStaff(" & Me.DTPPeriodFrom.Value.Year & "," & i & _
                                  ",N'" & Me.CombSalesman.Text.Trim & "'),0)," & _
                                  "IsNull(dbo.GetPremActualStaff(" & Me.DTPPeriodFrom.Value.Year & "," & i & _
                                  ",N'" & Me.CombSalesman.Text.Trim & "'),0)"

                Reader = cmd.ExecuteReader
                While Reader.Read
                    das.Tables(0).Rows.Add(New String() {i, MonthName(i), CDbl(Reader.Item(0)), CDbl(Reader.Item(1))})
                End While
                Reader.Close()
            Next
            cnn.Close()

            Dim rpt As New TargetStaff
            rpt.SetDataSource(das)

            'Cr Parameter

            Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
            Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            Dim crParameterFieldLocation As ParameterFieldDefinition
            Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
            crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields

            'Year PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("Year")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodFrom.Value.Year
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            'SalesMan PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("SalesMan")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.CombSalesman.Text
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            Me.CrystalReportViewer1.ReportSource = rpt

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub DTPPeriodFrom_ValueChanged(sender As System.Object, e As System.EventArgs) Handles DTPPeriodFrom.ValueChanged

    End Sub
End Class